package factory.one.service;

import factory.one.entity.Custom;
import factory.one.entity.Product;
import factory.one.pojo.common.SqlCondition;
import factory.one.utils.SqlUtil;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * @author sk.z
 * 2023/6/26
 * 客户
 **/
@Service
public class CustomService {

    private final JdbcTemplate jdbcTemplate;

    public CustomService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public static final Custom EmptyCustom;

    static {
        EmptyCustom = new Custom();
        EmptyCustom.setStatus(1);
        EmptyCustom.setName("未知");
        EmptyCustom.setPhone("");
    }

    public Long add(Custom custom) {
        PreparedStatementCreator preparedStatementCreator = con -> {
            PreparedStatement statement = con.prepareStatement(
                    "insert into custom(name,phone,remark,create_user,status) values(?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, custom.getName());
            statement.setString(2, custom.getPhone());
            statement.setString(3, custom.getRemark());
            statement.setString(4,custom.getCreateUser());
            statement.setInt(5, custom.getStatus());
            return statement;
        };
        GeneratedKeyHolder holder = new GeneratedKeyHolder();
        jdbcTemplate.update(preparedStatementCreator, holder);
        return holder.getKey() == null ? null : holder.getKey().longValue();
    }

    @CacheEvict(cacheNames = "CUSTOM_3H", key="#custom.id")
    public int update(Custom custom) {
        return jdbcTemplate.update("update custom set name=?, phone=?, remark=? where id=? ", ps -> {
            ps.setString(1, custom.getName());
            ps.setString(2, custom.getPhone());
            ps.setString(3,custom.getRemark());
            ps.setLong(4, custom.getId());
        });
    }

    public int del(Long id){
        return jdbcTemplate.update("update custom set status=0 where id=? ", ps -> {
            ps.setLong(1, id);
        });
    }

    public List<Custom> listByUser(String user) {
        if (StringUtils.hasText(user)){
            return jdbcTemplate.query("select * from custom where create_user = ? and status = 1 order by id desc ", new CustomRowMapper(), user);
        }
        return jdbcTemplate.query("select * from custom where status = 1 order by id desc ", new CustomRowMapper());
    }

    public List<Custom> listNormal() {
        return jdbcTemplate.query("select * from custom where status = 1 ", new CustomRowMapper());
    }

    public List<Custom> list(int page, int size) {
        return jdbcTemplate.query("select * from custom order by id desc " + SqlUtil.page(page, size), new CustomRowMapper());
    }

    public Long listCount() {
        return jdbcTemplate.queryForObject("select count(0) from custom", Long.class);
    }

    @Cacheable(cacheNames = "CUSTOM_3H", key="#id")
    public Custom getById(Long id) {
        try {
            return jdbcTemplate.queryForObject("select * from custom where id = " + id, new CustomRowMapper());
        }catch (EmptyResultDataAccessException empty){
            return EmptyCustom;
        }
    }

    public List<Custom> listByCondition(int page, int size, List<SqlCondition> conditions) {
        StringBuilder sql = new StringBuilder("select * from custom where 1=1 ");
        if (CollectionUtils.isEmpty(conditions)) {
            sql.append("order by id desc ").append(SqlUtil.page(page, size));
            return jdbcTemplate.query(sql.toString(), new CustomRowMapper());
        }

        Object[] args = new Object[conditions.size()];
        int[] types = new int[conditions.size()];
        for (int i = 0; i < conditions.size(); i++) {
            SqlCondition condition = conditions.get(i);
            sql.append("AND ").append(condition.getSqlDesc()).append(" ");
            args[i] = condition.getValue();
            types[i] = condition.getValueType();
        }
        sql.append("order by id desc ").append(SqlUtil.page(page, size));
        return jdbcTemplate.query(sql.toString(), args, types, new CustomRowMapper());
    }

    public Integer countByCondition(List<SqlCondition> conditions) {
        StringBuilder sql = new StringBuilder("select count(*) from custom where 1=1 ");
        if (CollectionUtils.isEmpty(conditions)) {
            return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
        }

        Object[] args = new Object[conditions.size()];
        int[] types = new int[conditions.size()];
        for (int i = 0; i < conditions.size(); i++) {
            SqlCondition condition = conditions.get(i);
            sql.append("AND ").append(condition.getSqlDesc()).append(" ");
            args[i] = condition.getValue();
            types[i] = condition.getValueType();
        }
        return jdbcTemplate.queryForObject(sql.toString(), args, types, Integer.class);
    }

    private static class CustomRowMapper implements RowMapper<Custom> {
        @Override
        public Custom mapRow(ResultSet rs, int rowNum) throws SQLException {
            Custom custom = new Custom();
            custom.setId(rs.getLong("id"));
            custom.setName(rs.getString("name"));
            custom.setPhone(rs.getString("phone"));
            custom.setRemark(rs.getString("remark"));
            custom.setCreateUser(rs.getString("create_user"));
            custom.setStatus(rs.getInt("status"));
            return custom;
        }
    }

}
